const db = require('../../db')
// exports.allList = (req, res) => {
//   // 查询入库表
//   const sqlInBound = `SELECT * FROM account_in_bound_detail`;
//   db.query(sqlInBound, (errorIn, resultsIn) => {
//     if (errorIn) {
//       return res.cc(errorIn);
//     }
//     // 存储入库表结果的对象
//     const resultMapIn = {};
//     let resultArrayIn = []
//     // 遍历入库表进行求和操作
//     resultsIn.forEach(item => {
//       const key = item.inBoundName + "_" + item.inBoundType;
//       if (resultMapIn[key]) {
//         resultMapIn[key].inBoundNum += item.inBoundNum;
//       } else {
//         resultMapIn[key] = {...item};
//       }
//       resultArrayIn = Object.values(resultMapIn);
//     })
//     const sqlOutBound = `SELECT * FROM account_out_bound_detail`;
//     db.query(sqlOutBound, (errorOut, resultsOut) => {
//       if (errorOut) {
//         return res.cc(errorOut);
//       }
//       // 存储入库表结果的对象
//       const resultMapOut = {};
//       let resultArrayOut = []
//       // 遍历入库表进行求和操作
//       resultsOut.forEach(item => {
//         const key = item.outBoundName + "_" + item.outBoundType;
//         if (resultMapOut[key]) {
//           resultMapOut[key].outBoundNum += item.outBoundNum;
//         } else {
//           resultMapOut[key] = {...item};
//         }
//         resultArrayOut = Object.values(resultMapOut);
//       })
//
//       // 将 resultArrayOut 转换为一个 Map，便于根据 key 快速查找数据
//       const outMap = new Map();
//       resultArrayOut.forEach(item => {
//         const key = item.outBoundName + '_' + item.outBoundType;
//         if (outMap.has(key)) {
//           const currentVal = outMap.get(key);
//           outMap.set(key, currentVal + item.outBoundNum);
//         } else {
//           outMap.set(key, item.outBoundNum);
//         }
//       });
//
//       // 计算 inBoundNum 减去 outBoundNum 的差值
//       const diffArray = resultArrayIn.map(item => {
//         const key = item.inBoundName + '_' + item.inBoundType;
//         const outBoundNum = outMap.get(key) || 0;
//         return {
//           ...item,
//           goodNum: item.inBoundNum - outBoundNum,
//         };
//       });
//       console.log('diffArray', diffArray);
//       return res.cc('获取物品列表成功', true, diffArray);
//     });
//   });
// }

exports.allList = (req, res) => {
  const {page = 1, size = 10, goodName = ''} = req.body;

  const offset = (page - 1) * size;
  const limit = parseInt(size);
  const countSQL = `
    SELECT COUNT(*) AS totalCount
    FROM
      (SELECT inBoundName, inBoundUnitName, inBoundType, SUM(inBoundNum) - IFNULL(SUM(outBoundNum), 0) AS inBoundNum
      FROM account_in_bound_detail LEFT JOIN account_out_bound_detail
      ON account_in_bound_detail.inBoundName = account_out_bound_detail.outBoundName
      AND account_in_bound_detail.inBoundUnitName = account_out_bound_detail.outBoundUnitName
      AND account_in_bound_detail.inBoundType = account_out_bound_detail.outBoundType
      GROUP BY inBoundName, inBoundUnitName, inBoundType
      HAVING inBoundName LIKE ?) AS T
  `;

  const sql = `
    SELECT
      inBound.inBoundName AS goodName,
      inBound.inBoundUnitName AS goodUnitName,
      inBound.inBoundType AS goodType,
      SUM(inBound.inBoundNum) - IFNULL(SUM(outBound.outBoundNum), 0) AS goodNum
    FROM
      (SELECT inBoundName, inBoundUnitName, inBoundType, SUM(inBoundNum) AS inBoundNum FROM account_in_bound_detail GROUP BY inBoundName, inBoundUnitName, inBoundType) inBound
    LEFT JOIN
      (SELECT outBoundName, outBoundUnitName, outBoundType, SUM(outBoundNum) AS outBoundNum FROM account_out_bound_detail GROUP BY outBoundName, outBoundUnitName, outBoundType) outBound
    ON inBound.inBoundName = outBound.outBoundName AND inBound.inBoundUnitName = outBound.outBoundUnitName AND inBound.inBoundType = outBound.outBoundType
    WHERE inBound.inBoundName LIKE ?
    GROUP BY inBound.inBoundName, inBound.inBoundUnitName, inBound.inBoundType
    LIMIT ? OFFSET ?
  `;
  db.query(countSQL, [`%${goodName}%`], (error, countResult) => {
    if (error) {
      return res.cc(error);
    }
    const total = countResult[0].totalCount;
    db.query(sql, [`%${goodName}%`, limit, offset], (error, results) => {
      if (error) {
        return res.cc(error);
      }
      return res.cc('获取物品列表成功', true, results, {
        total,
        page,
        limit
      });
    });
  });
}


